﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class TinhThanhServer
    Public Shared Function CreateTinhThanhFromReader(ByVal reader As SqlDataReader) As TinhThanh
        Dim tinhThanh As New TinhThanh()
        Try

            tinhThanh.ID = reader("ID")          
            If Not reader.IsDBNull(reader.GetOrdinal("NAME")) Then
                tinhThanh.NAME = DirectCast(reader("NAME"), String)
            End If
        Catch ex As Exception
        End Try
        Return tinhThanh
    End Function

    Public Shared Function GetListTinhThanh() As List(Of TinhThanh)
        Dim listTinhThanh As New List(Of TinhThanh)
        Try
            Dim reader As SqlDataReader
            Dim sql As String = "SELECT * FROM [TINH_THANH] as tt INNER JOIN(select city_id  from  [KHACH_SAN] where DISPLAY='1' and DELETED='0' group by city_id ) as tp on tp.city_id=tt.id where tt.DISPLAY='1' and tt.DELETED='0' order by tt.[order] "
            ''Dim sql As String = "SELECT * FROM DIA_DANH  where DISPLAY='1' and DELETED='0'  order by [order] desc"
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim tinhThanh As TinhThanh
                tinhThanh = CreateTinhThanhFromReader(reader)              
                listTinhThanh.Add(tinhThanh)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return listTinhThanh
    End Function

    Public Shared Function GetListTinhThanhPhoBien() As List(Of TinhThanh)
        Dim listTinhThanh As New List(Of TinhThanh)
        Try
            Dim reader As SqlDataReader

            Dim sql As String = "SELECT * FROM vwFrontEndDIA_DANH_HAP_DAN ddhd, DIA_DANH dd   where dd.ID=ddhd.HOME_TARGET_ID and dd.TINH_THANH_ID!='00000000-0000-0000-0000-000000000000' and DISPLAY='1' and DELETED='0'  order by HOME_ORDER desc"
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim tinhThanh As TinhThanh
                tinhThanh = CreateTinhThanhFromReader(reader)
                tinhThanh.ID = reader("TINH_THANH_ID")
                listTinhThanh.Add(tinhThanh)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return listTinhThanh
    End Function

    Public Shared Function CreateTinDuLichDictionaryFromReader(ByVal reader As SqlDataReader) As Dictionary(Of String, Object)
        Dim dictObjectKhachSan As New Dictionary(Of String, Object)
        Try
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PID", (reader("PID")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PTitle", (reader("PTitle")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PDesc", (reader("PDesc")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PImg", (reader("PImg")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PCreateDay", (reader("PCreateDay")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PContent", (reader("PContent")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PCID", (reader("PCID")))
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "PLink", (reader("PLink")))
        Catch ex As Exception
        End Try
        Return dictObjectKhachSan
    End Function


    Public Shared Function GetListTinDuLich(Optional ByVal indexStar As Integer = 0) As List(Of Dictionary(Of String, Object))
        Dim listTinDulich As New List(Of Dictionary(Of String, Object))
        Try
            Dim sql As String = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PCreateDay desc) as row, [PID],[PTitle] ,[PTitleEN] ,[PDesc] ,[PDescEN]"
            sql &= " ,[PImg] ,[PLink] ,[PCID] ,[PGroup] ,[PMetaKey],[PMetaDesc]"
            sql &= " ,[PAvatar]  ,[POrder],[PDisplay] ,[PAdmin] ,[PCreateDay]"
            sql &= " ,[PModifyDay] ,[PUID] ,[PView] ,[PParams]"
            sql &= " ,[PContent],[PContentEN],[PImgBig]"
            sql &= String.Format("  FROM [Post] where PDisplay='1') b WHERE b.row > {0} and b.row <= {1}", indexStar * Util.GetConfigKey("page_num"), (indexStar + 1) * Util.GetConfigKey("page_num"))
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim dictObjectKhachSan As New Dictionary(Of String, Object)
                dictObjectKhachSan = CreateTinDuLichDictionaryFromReader(reader)
                listTinDulich.Add(dictObjectKhachSan)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try

        Return listTinDulich
    End Function
    Public Shared Function GetCountTinDuLich(Optional ByVal indexStar As Integer = 0) As Integer
        Dim num As Integer
        Try
            Dim sql As String = "SELECT count(*) as num"
            sql &= "  FROM [Post] where PDisplay='1'"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                num = DirectCast(reader("num"), Integer)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try

        Return num
    End Function

    Public Shared Function GetListTinDuLichTheoPCID(ByVal pcID As Integer, ByVal createDay As Date) As List(Of Dictionary(Of String, Object))
        Dim listTinDulich As New List(Of Dictionary(Of String, Object))
        Try
            Dim sql As String = "SELECT top 5 [PID],[PTitle] ,[PTitleEN] ,[PDesc] ,[PDescEN]"
            sql &= " ,[PImg] ,[PLink] ,[PCID] ,[PGroup] ,[PMetaKey],[PMetaDesc]"
            sql &= " ,[PAvatar]  ,[POrder],[PDisplay] ,[PAdmin] ,[PCreateDay]"
            sql &= " ,[PModifyDay] ,[PUID] ,[PView] ,[PParams]"
            sql &= " ,[PContent],[PContentEN],[PImgBig]"
            sql &= "  FROM [Post] where PDisplay='1' and PCreateDay <'" & createDay & "'  and PCID='" & pcID & "' order by PCreateDay desc"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim dictObjectKhachSan As New Dictionary(Of String, Object)
                dictObjectKhachSan = CreateTinDuLichDictionaryFromReader(reader)
                listTinDulich.Add(dictObjectKhachSan)
            End While
            sql = "SELECT top 5 [PID],[PTitle] ,[PTitleEN] ,[PDesc] ,[PDescEN]"
            sql &= " ,[PImg] ,[PLink] ,[PCID] ,[PGroup] ,[PMetaKey],[PMetaDesc]"
            sql &= " ,[PAvatar]  ,[POrder],[PDisplay] ,[PAdmin] ,[PCreateDay]"
            sql &= " ,[PModifyDay] ,[PUID] ,[PView] ,[PParams]"
            sql &= " ,[PContent],[PContentEN],[PImgBig]"
            sql &= "  FROM [Post] where PDisplay='1' and PCreateDay >'" & createDay & "'  and PCID='" & pcID & "' order by PCreateDay desc"
            reader.Close()
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim dictObjectKhachSan As New Dictionary(Of String, Object)
                dictObjectKhachSan = CreateTinDuLichDictionaryFromReader(reader)
                listTinDulich.Add(dictObjectKhachSan)
            End While
            Connection.Dispose()

        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try

        Return listTinDulich
    End Function

    Public Shared Function GetTinDuLichTheoID(ByVal ID As Integer) As Dictionary(Of String, Object)
        Dim dictObjectKhachSan As New Dictionary(Of String, Object)
        Try
            Dim sql As String = "SELECT top " + Util.GetConfigKey("page_num") + " [PID],[PTitle] ,[PTitleEN] ,[PDesc] ,[PDescEN]"
            sql &= " ,[PImg] ,[PLink] ,[PCID] ,[PGroup] ,[PMetaKey],[PMetaDesc]"
            sql &= " ,[PAvatar]  ,[POrder],[PDisplay] ,[PAdmin] ,[PCreateDay]"
            sql &= " ,[PModifyDay] ,[PUID] ,[PView] ,[PParams]"
            sql &= " ,[PContent],[PContentEN],[PImgBig]"
            sql &= "  FROM [Post] where PDisplay='1' and PID='" & ID & "' order by PCreateDay desc"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                dictObjectKhachSan = CreateTinDuLichDictionaryFromReader(reader)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try

        Return dictObjectKhachSan
    End Function
End Class
